<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ page language="java" import="java.sql.*"%>
<%
	Class.forName("org.postgresql.Driver");
	Connection conn = DriverManager.getConnection(
			"jdbc:postgresql://localhost:5432/cse132b", "cse132blogin",
			"1234567890");
	Statement stmt, stmt1, stmt2;

	if (request.getParameter("ACTION") != null) {
		if (request.getParameter("ACTION").equals("Insert")) {
			int NewID = 0;

			PreparedStatement statement = conn
					.prepareStatement("INSERT INTO degrees_offered(department_id, name, type_degree, lower_units_reqd, upper_units_reqd, elective_units_reqd) VALUES (?,?,?,?,?,?)");

			statement.setInt(1, Integer.parseInt(request
					.getParameter("sltDepartment")));
			statement.setString(2, request.getParameter("txtName"));
			statement.setString(3, request.getParameter("sltType"));
			statement.setInt(4,
					Integer.parseInt(request.getParameter("txtLower")));
			statement.setInt(5,
					Integer.parseInt(request.getParameter("txtUpper")));
			statement.setInt(6,
					Integer.parseInt(request.getParameter("txtElective")));

			statement.executeUpdate();
			statement.close();
		}

		if (request.getParameter("ACTION").equals("Update")) {
			int EditID = Integer.valueOf(request
					.getParameter("PARAMETER"));

			PreparedStatement statement = conn
					.prepareStatement("UPDATE degrees_offered SET department_id = ?, name = ?, type_degree = ?, lower_units_reqd = ?, upper_units_reqd = ?, elective_units_reqd = ? WHERE degrees_id = ?");

			statement.setInt(1, Integer.parseInt(request
					.getParameter("sltDepartment"
							+ String.valueOf(EditID))));
			statement.setString(
					2,
					request.getParameter("txtName"
							+ String.valueOf(EditID)));

			statement.setString(
					3,
					request.getParameter("sltType"
							+ String.valueOf(EditID)));
			statement.setInt(
					4,
					Integer.parseInt(request.getParameter("txtLower"
							+ String.valueOf(EditID))));
			statement.setInt(
					5,
					Integer.parseInt(request.getParameter("txtUpper"
							+ String.valueOf(EditID))));

			statement.setInt(
					6,
					Integer.parseInt(request.getParameter("txtElective"
							+ String.valueOf(EditID))));
			
			statement.setInt(7, EditID);

			statement.executeUpdate();
			statement.close();

		}

		if (request.getParameter("ACTION").equals("Delete")) {
			int EditID = Integer.valueOf(request
					.getParameter("PARAMETER"));

			PreparedStatement statement = conn
					.prepareStatement("DELETE FROM degrees_offered WHERE degrees_id = ?");
			statement.setInt(1, EditID);
			statement.executeUpdate();
			statement.close();
		}
	}
%>
<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
	function Insert() {
		document.getElementById("sltDepartment").required = true;
		document.getElementById("txtName").required = true;
		document.getElementById("sltType").required = true;
		document.getElementById("txtUpper").required = true;
		document.getElementById("txtLower").required = true;
		document.getElementById("ACTION").value = "Insert";
	}
	function Update(row) {
		document.getElementById("sltDepartment" + row).required = true;
		document.getElementById("txtName" + row).required = true;
		document.getElementById("sltType" + row).required = true;
		document.getElementById("txtUpper" + row).required = true;
		document.getElementById("txtLower" + row).required = true;
		document.getElementById("ACTION").value = "Update";
		document.getElementById("PARAMETER").value = row;
	}
	function Delete(row) {
		document.getElementById("ACTION").value = "Delete";
		document.getElementById("PARAMETER").value = row;
	}
</script>
</head>
<body>
	<table border="1">
		<tr>
			<td valign="top" width="40%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>
				<form id="myForm" method="post" action="degree.jsp">
					<p align="center">
						<font size="6">Degree Requirements' Info Submission</font>
					</p>
					<table border="1">
						<tr>
							<th>Department Name</th>
							<th>Degree Name</th>
							<th>Type</th>
							<th>Upper Units<br>Required
							</th>
							<th>Lower Units<br>Required
							</th>
							<th>Elective Units<br>Required
							</th>
							<th>Action</th>
						</tr>
						<%---------- Add ----------%>
						<td><select name="sltDepartment" id="sltDepartment">
								<option value="">Select a Department</option>
								<%
									stmt = conn.createStatement();
									ResultSet rsDepartment = stmt
											.executeQuery("SELECT * FROM Departments");
									while (rsDepartment.next()) {
								%>
								<option value="<%=rsDepartment.getInt("department_id")%>"><%=rsDepartment.getString("name")%></option>
								<%
									}
									rsDepartment.close();
									stmt.close();
								%>
						</select></td>
						<td><input type="text" name="txtName" id="txtName"></td>
						<td><select name="sltType" id="sltType">
								<option value="">Select a Type</option>
								<option value="BS">BS</option>
								<option value="MS">MS</option>
								<option value="PhD">PhD</option>
						</select></td>
						<td align="center"><input type="text" name="txtUpper"
							id="txtUpper" pattern="\d+" size="2"></td>
						<td align="center"><input type="text" name="txtLower"
							id="txtLower" pattern="\d+" size="2"></td>
						<td align="center"><input type="text" name="txtElective"
							id="txtElective" pattern="\d+" size="2"></td>
						<td align="center"><input type="submit" value="Insert"
							onclick="Insert()"></td>
						<%---------- Edit ----------%>
						<%
							stmt = conn.createStatement();
							ResultSet rsDegrees = stmt
									.executeQuery("SELECT * FROM degrees_offered");

							while (rsDegrees.next()) {
						%>
						<tr>
							<td><select
								name="sltDepartment<%=rsDegrees.getInt("degrees_id")%>"
								id="sltDepartment<%=rsDegrees.getInt("degrees_id")%>">
									<option value="">Select a Department</option>

									<%
										stmt1 = conn.createStatement();
											rsDepartment = stmt1.executeQuery("SELECT * FROM Departments");
											while (rsDepartment.next()) {
												if (rsDepartment.getInt("department_id") == rsDegrees
														.getInt("department_id")) {
									%>
									<option selected
										value="<%=rsDepartment.getInt("department_id")%>"><%=rsDepartment.getString("name")%></option>
									<%
										} else {
									%>
									<option value="<%=rsDepartment.getInt("department_id")%>"><%=rsDepartment.getString("name")%></option>
									<%
										}
											}
											rsDepartment.close();
											stmt1.close();
									%>
							</select></td>
							<td><input type="text"
								name="txtName<%=rsDegrees.getInt("degrees_id")%>"
								id="txtName<%=rsDegrees.getInt("degrees_id")%>"
								value="<%=rsDegrees.getString("name")%>"></td>
							<td><select
								name="sltType<%=rsDegrees.getInt("degrees_id")%>"
								id="sltType<%=rsDegrees.getInt("degrees_id")%>">
									<option value="">Select a Type</option>
									<%
										if (rsDegrees.getString("type_degree").equals("BS")) {
									%>
									<option value="BS" selected>BS</option>
									<%
										} else {
									%>
									<option value="BS">BS</option>
									<%
										}
									%>
									<%
										if (rsDegrees.getString("type_degree").equals("MS")) {
									%>
									<option value="MS" selected>MS</option>
									<%
										} else {
									%>
									<option value="MS">MS</option>
									<%
										}
									%>
									<%
										if (rsDegrees.getString("type_degree").equals("PhD")) {
									%>
									<option value="PhD" selected>PhD</option>
									<%
										} else {
									%>
									<option value="PhD">PhD</option>
									<%
										}
									%>
							</select></td>
							<td align="center"><input size="2" type="text"
								name="txtUpper<%=rsDegrees.getInt("degrees_id")%>"
								id="txtUpper<%=rsDegrees.getInt("degrees_id")%>"
								value="<%=rsDegrees.getString("upper_units_reqd")%>"></td>
							<td align="center"><input size="2" type="text"
								name="txtLower<%=rsDegrees.getInt("degrees_id")%>"
								id="txtLower<%=rsDegrees.getInt("degrees_id")%>"
								value="<%=rsDegrees.getString("lower_units_reqd")%>"></td>
							<td align="center"><input size="2" type="text"
								name="txtElective<%=rsDegrees.getInt("degrees_id")%>"
								id="txtElective<%=rsDegrees.getInt("degrees_id")%>"
								value="<%=rsDegrees.getString("elective_units_reqd")%>"></td>
							<td><input type="submit" value="Update"
								onclick="Update(<%=rsDegrees.getInt("degrees_id")%>)"> <input
								type="submit" value="Delete"
								onclick="Delete(<%=rsDegrees.getInt("degrees_id")%>)"></td>
						</tr>
						<%
							}
						%>
					</table>
					<input type="hidden" id="ACTION" name="ACTION" value="None">
					<input type="hidden" id="PARAMETER" name="PARAMETER" value="">
				</form>
			</td>
		</tr>
	</table>
</body>
</html>
<%
	// Close the Connection
	conn.close();
%>